package databaseHelper;

import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;
import java.sql.Blob;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.Stack;

import javax.imageio.ImageIO;
import javax.sql.rowset.serial.SerialBlob;
import javax.sql.rowset.serial.SerialException;

import org.zkoss.image.Image;
import org.zkoss.zhtml.Var;

import Misc.Variables;

import datatypes.Exam;
import datatypes.Exercise;
import datatypes.FreeTextExercise;
import datatypes.FreeTextQuestion;
import datatypes.GradingKey;
import datatypes.ImageMultipleChoiceExercise;
import datatypes.ImageMultipleChoiceQuestion;
import datatypes.MultipleChoiceExercise;
import datatypes.MultipleChoiceQuestion;

public class CreateExamDAO {
		
		DataSource ds = DataSource.INSTANCE;
		
		
		/**
	     * 
	     * Insert a given Exam into the databse
	     * 
	     * @param exam
	     * @param userId
	     * @return
	     */
	    public boolean insert(Exam exam, int userId) {
	    	System.out.println("SAVING EXAM");
	    	
	    	Stack<Exercise> exercises = (Stack<Exercise>) exam.getExercises().clone();
	    	
//	    	double[] maxPointsDouble = exam.getMaxPointsPerExercise();
//	    	Object[] maxPointsObject = new Object[maxPointsDouble.length];
//	    	
//	    	for(int i=0; i<maxPointsDouble.length; i++){
//	    		maxPointsObject[i] = maxPointsDouble[i];
//	    	}
//
//	        org.hsqldb.types.Type type = org.hsqldb.types.Type.SQL_DOUBLE;
//	    	JDBCArrayBasic maxPointsSQL = new JDBCArrayBasic(maxPointsObject, type);
//	    	
//	    	double[] gradingDouble = exam.getGradingArray();
//	    	Object[] gradingObject = new Object[gradingDouble.length];
//	    	
//	    	for(int i=0; i<gradingDouble.length; i++){
//	    		gradingObject[i] = gradingDouble[i];
//	    	}
//
//	    	JDBCArrayBasic gradingSQL = new JDBCArrayBasic(maxPointsObject, type);
//	    	
//	    	/*
//	    	 * INSERT INTO EXAM
//	    	 */
//	    	boolean insertCorrect = execute("INSERT INTO exam(subjectId, subjectName, examDate, evaluationBegin, evaluationEnd, numberOfParticipants, numberOfExercises, maxPointsPerExercise, gradingArray) " +
//					"VALUES ('"+exam.getSubjectIdentification()+"','"+exam.getSubjectName() +"','"
//					+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(exam.getExamDate()) +"','" +
//					new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(exam.getEvaluationBeginDate()) +"','" +
//					new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(exam.getEvaluationEndDate()) +"','" +
//					exam.getNumberOfParticipants() +"','" +
//					exam.getNumberOfExercises()  +"'," +
//					maxPointsSQL	+","+
//					gradingSQL
//					+")");
//	    	    	
	    	
	    	double[] maxPointsDouble = exam.getMaxPointsPerExercise();
	    	
	    	double[] gradingDouble = exam.getGradingArray();
	    	
	    	boolean insertCorrect = execute("INSERT INTO exam(subjectId, subjectName, examDate, evaluationBegin, evaluationEnd, " +
	    			"numberOfParticipants, facility, faculty, professorship, examiner, subjectFirstPossibility, subjectSecondPossibility) " +
					"VALUES ('"+exam.getSubjectIdentification()+"','"+
	    			exam.getSubjectName() +"','" + 
					new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(exam.getExamDate()) +"','" +
					new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(exam.getEvaluationBeginDate()) +"','" +
					new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(exam.getEvaluationEndDate()) +"'," +
					exam.getNumberOfParticipants() +",'" +
					exam.getFacility() +"','" +
					exam.getFaculty() +"','" +
					exam.getProfessorship() +"','" +
					exam.getExaminer() +"','" +
					exam.getSubjectFirstPossibility() +"','" +
					exam.getSubjectSecondPossibility()+"'"
					+")");

			int examId = -1;
	    	/*
	    	 * GET ID of currently inserted exam and make an entry into the exam_user_rel-TABLE
	    	 */
	    	try{
				Statement st = ds.getStatement();
				ResultSet rs = st.executeQuery("SELECT MAX(id) FROM exam ORDER BY id DESC");
				if(rs.next()){
					examId = rs.getInt(1);
					System.out.println("Exam-Id = " + examId);
				}
				if(insertCorrect){
		    		insertCorrect = execute("INSERT INTO exam_user_rel(subjectId, userId) " +
		    								"VALUES ('"+examId+"','"+userId+"')");
		    	}
			} catch (SQLException e) {
				e.printStackTrace();
			}
	    	
	    	exam.setId(examId);
	    	
	    	/**
	    	 * INSERT THE GRADING ARRAY INTO THE TABLE
	    	 */
	    	String grade="1.0";
	    	for(int i=0; i<gradingDouble.length; i++){
	    		switch (i) {
					case 0:
						grade="1.0";
						break;
					case 1:
						grade="1.3";
						break;
					case 2:
						grade="1.7";
						break;
					case 3:
						grade="2.0";
						break;
					case 4:
						grade="2.3";
						break;
					case 5:
						grade="2.7";
						break;
					case 6:
						grade="3.0";
						break;
					case 7:
						grade="3.3";
						break;
					case 8:
						grade="3.7";
						break;
					case 9:
						grade="4.0";
						break;
					case 10:
						grade="4.3";
						break;
					case 11:
						grade="4.7";
						break;
					default:
						
						break;
						
				}
			    double pointsToArchive = gradingDouble[i];
		    	
	    		insertCorrect = execute("INSERT INTO exam_grading (examId, grade, pointsNeeded) " +
						"VALUES (" + examId+"," +
									"'" + grade  +"'," +
									pointsToArchive
						+")");
	    	}
	    	
	    	
	    	
	    	System.out.println("EXERCISE-SIZE: "+exercises.size());
	    	/*
	    	 * INSERT THE EXERCISES INTO THE EXERCISE_TABLE
	    	 */
	    	
	    	Stack<Exercise> real_Exercises = exam.getExercises();
	    	
	    	
	    	while(exercises.size()>0){
	    		int curIndex = exercises.size();
	    		Exercise curExercise = exercises.pop();
	    		
	    		Exercise realCurExercise = real_Exercises.get(curIndex-1);
	    		
	    		if(curExercise instanceof MultipleChoiceExercise){
	    			MultipleChoiceExercise MCexercise = (MultipleChoiceExercise) curExercise;
	    			// TODO: EvaluationType einbauen
	    			insertCorrect = execute("INSERT INTO multipleChoiceExercise (examId, exerciseNumber, question, headline, maxPointsInExercise, rightSelectionPoints, evaluationType) " +
	    					"VALUES (" + examId+"," +
	    								curIndex+"," +
		    						"'" + MCexercise.getQuestion() +"'," +
		    						"'" + MCexercise.getHeadline() +"'," +
	    								MCexercise.getMaxPoints() +","+
	    								MCexercise.getPointsPerRightSelection() +","+
	    								MCexercise.getEvaluationType()
	    					+")");
	    			
	    			/*
	    	    	 * GET ID of currently MCexercise
	    	    	 */
	    			int exerciseId = -1;
	    	    	try{
	    				Statement st = ds.getStatement();
	    				ResultSet rs = st.executeQuery("SELECT MAX(id) FROM multipleChoiceExercise ORDER BY id DESC");
	    				
	    				if(rs.next()){
	    					exerciseId = rs.getInt(1);
	    					System.out.println("Exercise-Id = " + exerciseId);
	    				}
	    			} catch (SQLException e) {
	    				e.printStackTrace();
	    			}
	    	    	
	    	    	realCurExercise.setIndex(exerciseId);
	    	    	/*
	    	    	 *  Enter the MCQuestions
	    	    	 */
	    	    	List<MultipleChoiceQuestion> MCquestions = MCexercise.getQuestions();
	    	    	
	    	    	for(int i=0; i<MCquestions.size(); i++){
	    	    		MultipleChoiceQuestion curMCQuestion = MCquestions.get(i);
	    	    		    		    	
	    		    	double maxPointsInQuestion = curMCQuestion.getAnswers().size() * MCexercise.getPointsPerRightSelection();
	    		    	
	    	    		boolean insert = 
	    	    				execute("INSERT INTO multipleChoiceQuestion (exerciseId, question, maxPointsInQuestion)" +
	    	    				"VALUES (" +
	    	    					exerciseId+", " +
	    	    					"'"	+curMCQuestion.getQuestion() +"', " +
	    	    					maxPointsInQuestion +
	    	    					")"
	    	    				);
	    	    		
	    	    		/*
		    	    	 * GET ID of currently MCexercise
		    	    	 */
		    			int mpChoiceQuestionId = -1;
		    	    	try{
		    				Statement st = ds.getStatement();
		    				ResultSet rs = st.executeQuery("SELECT MAX(id) FROM multipleChoiceQuestion ORDER BY id DESC");
		    				
		    				if(rs.next()){
		    					mpChoiceQuestionId = rs.getInt(1);
		    					System.out.println("multipleChoiceQuestion-Id = " + mpChoiceQuestionId);
		    				}
		    			} catch (SQLException e) {
		    				e.printStackTrace();
		    			}
	    	    		
	    	    		int index = 0;
	    	    		for(String answerPossibility : curMCQuestion.getAnswers()){
	    	    			insert = 
		    	    				execute("INSERT INTO multipleChoiceQuestionAnswerPossibilities (multipleChoiceQuestionId, answerPossibilities, answerIndex)" +
		    	    				"VALUES (" +
		    	    						mpChoiceQuestionId+", " +
		    	    					"'"	+answerPossibility +"', " +
		    	    					index +
		    	    					")"
		    	    				);
		    	    		index++;
	    	    		}
	    	    		
	    	    		for(int boxToMark : curMCQuestion.getCorrectAnswers()){
	    	    			insert = 
		    	    				execute("INSERT INTO multipleChoiceQuestionSolutions (multipleChoiceQuestionId, indexOfBoxToMark)" +
		    	    				"VALUES (" +
		    	    						mpChoiceQuestionId+", " +
		    	    						+ boxToMark +
		    	    					")"
		    	    				);
	    	    		}
	    	    		
	    	    	}
	    			
	    		}
	    		
	    		
	    		if(curExercise instanceof ImageMultipleChoiceExercise){
	    			
	    			ImageMultipleChoiceExercise imageMCexercise = (ImageMultipleChoiceExercise) curExercise;
	    			// TODO: EvaluationType einbauen
	    			insertCorrect = execute("INSERT INTO imageMultipleChoiceExercise (examId, exerciseNumber, question, headline, maxPointsInExercise, rightSelectionPoints, evaluationType) " +
	    					"VALUES (" + examId+"," +
	    								curIndex+"," +
		    						"'" + imageMCexercise.getQuestion() +"'," +
		    						"'" + imageMCexercise.getHeadline() +"'," +
	    								imageMCexercise.getMaxPoints() +","+
	    								imageMCexercise.getPointsPerRightSelection() +","+
	    								imageMCexercise.getEvaluationType()
	    					+")");
	    			
	    			/*
	    	    	 * GET ID of currently ImageMCexercise
	    	    	 */
	    			int exerciseId = -1;
	    	    	try{
	    				Statement st = ds.getStatement();
	    				ResultSet rs = st.executeQuery("SELECT MAX(id) FROM imageMultipleChoiceExercise ORDER BY id DESC");
	    				
	    				if(rs.next()){
	    					exerciseId = rs.getInt(1);
	    					System.out.println("Exercise-Id = " + exerciseId);
	    				}
	    			} catch (SQLException e) {
	    				e.printStackTrace();
	    			}
	    	    	
	    	    	imageMCexercise.setIndex(exerciseId);
	    	    	realCurExercise.setIndex(exerciseId);
	    	    	
	    	    	
	    	    	/*
	    	    	 *  Enter the MCQuestions
	    	    	 */
	    	    	List<ImageMultipleChoiceQuestion> MCquestions = imageMCexercise.getQuestions();
	    	    	
	    	    	List<ImageMultipleChoiceQuestion> realMCquestions = ((ImageMultipleChoiceExercise) realCurExercise).getQuestions();
	    	    	
	    	    	
	    	    	for(int i=0; i<MCquestions.size(); i++){
	    	    		ImageMultipleChoiceQuestion curImageMCQuestion = MCquestions.get(i);
	    	    		
	    	    		double maxPointsInQuestion = curImageMCQuestion.getAnswers().size() * imageMCexercise.getPointsPerRightSelection();
	    	    		
	    	    		boolean insert = 
	    	    				execute("INSERT INTO imageMultipleChoiceQuestion (exerciseId, question, maxPointsInQuestion)" +
	    	    				"VALUES (" +
	    	    					exerciseId+", " +
	    	    					"'"	+curImageMCQuestion.getQuestion() +"', " +
	    	    					maxPointsInQuestion +
	    	    					")"
	    	    				);
	    	    		
	    	    		/*
		    	    	 * GET ID of currently MCexercise
		    	    	 */
		    			int imageMpChoiceQuestionId = -1;
		    	    	try{
		    				Statement st = ds.getStatement();
		    				ResultSet rs = st.executeQuery("SELECT MAX(id) FROM imageMultipleChoiceQuestion ORDER BY id DESC");
		    				
		    				if(rs.next()){
		    					imageMpChoiceQuestionId = rs.getInt(1);
		    					System.out.println("imageMultipleChoiceQuestion-Id = " + imageMpChoiceQuestionId);
		    				}
		    			} catch (SQLException e) {
		    				e.printStackTrace();
		    			}
		    	    	
		    	    	realMCquestions.get(i).setId(imageMpChoiceQuestionId);
		    	    	MCquestions.get(i).setId(imageMpChoiceQuestionId);
		    			
		    	    	/**
		    	    	 * Transforming the images to bytes
		    	    	 */
	    	    		List<Image> imMpQuestion = curImageMCQuestion.getAnswers();
	    	    		List<String> imMPQuestionLocation = new ArrayList<String>();
	    	    		
	    	    		for(int j=0; j<imMpQuestion.size(); j++){
	    	    			
		    	    		String savingSpaceBase = Variables.SAVINGSPACE_IMAGEEXERCISE;
	    	    			
		    	    		try {
		    	    			savingSpaceBase = savingSpaceBase + "exerciseId"+imageMCexercise.getIndex()+"/question"+i;
		    	    			File newDirectories = new File(savingSpaceBase);
		    	    			newDirectories.mkdirs();
		    	    			
		    	    			
		    	    			savingSpaceBase = newDirectories.getPath() +"/imagePossibility"+j+".png";
		    	    			
								BufferedImage bfi = ImageIO.read(imMpQuestion.get(j).getStreamData());
								ImageIO.write(bfi, "png", new File(savingSpaceBase));
		    	    		} catch (IOException e) {
								e.printStackTrace();
							}
		    	    		
		    	    		System.out.println("MP-Choice-Image: "+savingSpaceBase);
		    	    		
		    	    		savingSpaceBase = savingSpaceBase.replace("\\", "/");
		    	    		
		    	    		System.out.println("MP-Choice-Image: "+savingSpaceBase);
		    	    		
		    	    		insert = 
		    	    				execute("INSERT INTO imageMultipleChoiceQuestionAnswerPossibilities (imageMultipleChoiceQuestionId, answerImage, answerIndex)" +
		    	    				"VALUES (" +
		    	    					imageMpChoiceQuestionId+", " +
		    	    					"'"	+savingSpaceBase +"', " +
		    	    					j +
		    	    					")"
		    	    				);

		    	    		imMPQuestionLocation.add(savingSpaceBase);
	    	    		}
	    	    		
	    	    		curImageMCQuestion.setAnswerPossibilitiesLocation(imMPQuestionLocation);	    	    		
	    	    		
	    	    		for(int boxToMark : curImageMCQuestion.getCorrectAnswers()){
	    	    			insert = 
		    	    				execute("INSERT INTO imageMultipleChoiceQuestionSolutions (imageMultipleChoiceQuestionId, indexOfBoxToMark)" +
		    	    				"VALUES (" +
		    	    						imageMpChoiceQuestionId+", " +
		    	    						+ boxToMark +
		    	    					")"
		    	    				);
	    	    		}
	    	    		
	    	    	}
	    	    	
	    			
	    		}
	    		
	    		if(curExercise instanceof FreeTextExercise){
	    			FreeTextExercise freeTextExercise = (FreeTextExercise) curExercise;
	    			
	    			insertCorrect = execute("INSERT INTO freeTextExercise (examId, exerciseNumber, question, headline, maxPointsInExercise) " +
	    					"VALUES (" + examId+"," +
	    								curIndex+"," +
		    						"'" + freeTextExercise.getQuestion() +"'," +
		    						"'" + freeTextExercise.getHeadline() +"'," +
	    								freeTextExercise.getMaxPoints()
	    					+")");
	    			
	    			/*
	    	    	 * GET ID of currently MCexercise
	    	    	 */
	    			int freeTextExerciseId = -1;
	    	    	try{
	    				Statement st = ds.getStatement();
	    				ResultSet rs = st.executeQuery("SELECT MAX(id) FROM freeTextExercise ORDER BY id DESC");
	    				
	    				if(rs.next()){
	    					freeTextExerciseId = rs.getInt(1);
	    					System.out.println("freeTextExercise-Id = " + freeTextExerciseId);
	    				}
	    			} catch (SQLException e) {
	    				e.printStackTrace();
	    			}
	    	    	
	    	    	realCurExercise.setIndex(freeTextExerciseId);
	    	    	
	    	    	/*
	    	    	 *  Enter the MCQuestions
	    	    	 */
	    	    	List<FreeTextQuestion> freeTextQuestions = freeTextExercise.getFreeTextQuestions();
	    	    	
	    	    	for(int i=0; i<freeTextQuestions.size(); i++){
	    	    		FreeTextQuestion curFreeTextQuestion = freeTextQuestions.get(i);
	    	    		    		    	
	    		    	
	    	    		boolean insert = 
	    	    				execute("INSERT INTO freeTextQuestion (exerciseId, question, answer, answerFieldHeight, achievablePoints)" +
	    	    				"VALUES (" +
	    	    					freeTextExerciseId+", " +
	    	    					"'"	+curFreeTextQuestion.getFreeTextQuestion() +"', "  +
			    	    			"'"	+curFreeTextQuestion.getAnswer() +"', "  +
			    	    			"'"	+curFreeTextQuestion.getAnswerfieldHeigt() +"', "+
			    	    			"'"	+curFreeTextQuestion.getArchievablePoints()+"'"+
	    	    					")"
	    	    				);
	    	    		
	    	    	}
	    		}
	    		
	    	}
	    	

	    	System.out.println("FINISHED SAVING EXAM");
	    	
			return insertCorrect;
	    }
		
	    
	    /**
		 * 
		 * Getting an exam with a given id from the database
		 * 
		 * @param id
		 * @return
		 */
		public Exam getExamInformation(int id){
			
			String query = "SELECT * from exam WHERE id="+id;
			
			Statement st;
			try {
				st = ds.getStatement();
				
				ResultSet rs = st.executeQuery(query);
			
				if(!rs.next()){
					return new Exam();
				}
				
				Exam exam  = new Exam();
				exam.setId(id);
				exam.setSubjectIdentification(rs.getString(2));
				exam.setSubjectName(rs.getString(3));
				exam.setExamDate(rs.getDate(4));
				exam.setEvaluationBeginDate(rs.getDate(5));
				exam.setEvaluationEndDate(rs.getDate(6));
				exam.setNumberOfParticipants(rs.getInt(7));
				exam.setFacility(rs.getString(8));
				exam.setFaculty(rs.getString(9));
				exam.setProfessorship(rs.getString(10));
				exam.setExaminer(rs.getString(11));
				exam.setSubjectFirstPossibility(rs.getString(12));
				exam.setSubjectSecondPossibility(rs.getString(13));
				
				return exam;

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return new Exam();
		}
		
		
		/**
		 * returns the grading Key to given Exam
		 * 
		 * @param examId
		 * @return
		 */
		public GradingKey getGradingKey(int examId){
			
			GradingKey gradingKey =  new GradingKey();
			
			String query = "Select pointsNeeded FROM exam_grading WHERE examId="+examId+" ORDER BY GRADE ASC";
			
			Statement st;
			try {
				st = ds.getStatement();
				ResultSet rs = st.executeQuery(query);
				
				rs.next();
				gradingKey.setgrade10(rs.getDouble(1));
				rs.next();
				gradingKey.setgrade13(rs.getDouble(1));
				rs.next();
				gradingKey.setgrade17(rs.getDouble(1));
				rs.next();
				gradingKey.setgrade20(rs.getDouble(1));
				rs.next();
				gradingKey.setgrade23(rs.getDouble(1));
				rs.next();
				gradingKey.setgrade27(rs.getDouble(1));
				rs.next();
				gradingKey.setgrade30(rs.getDouble(1));
				rs.next();
				gradingKey.setgrade33(rs.getDouble(1));
				rs.next();
				gradingKey.setgrade37(rs.getDouble(1));
				rs.next();
				gradingKey.setgrade40(rs.getDouble(1));
				rs.next();
				gradingKey.setgrade43(rs.getDouble(1));
				rs.next();
				gradingKey.setgrade47(rs.getDouble(1));
			
			}catch (Exception e) {
				return new GradingKey();
			}
			
			return gradingKey;
		}
	    
		/**
		 * 
		 * method returning the exercises of an exam. Needed for editing exams!
		 * 
		 * @param examId
		 * @return
		 */
		public Stack<Exercise> getExercisesToExam(int examId){
			ExamDAO newExamDAO = new ExamDAO();

			
			Stack<Exercise> exerciseList = new Stack<Exercise>();
			
			int nbrOfExercises = newExamDAO.getNumberOfExercises(examId);
			
			String query;
			
			for(int i=1; i<=nbrOfExercises; i++){
				
				Exercise curExercise;
				
				int exerciseId = -1;
				String headline = "";
				String question = "";
				double rightSelectionPoints = -1;
				int evaluationType = -1;
				double maxPointsInExercise = -1;
				
				
				int exerciseType = -1;
				
				query ="SELECT * from multipleChoiceExercise WHERE examId="+examId+" AND exerciseNumber="+i;

				System.out.println(query);
				
				try {
					Statement st = ds.getStatement();	
					ResultSet rs = st.executeQuery(query);
					
					if(rs.next()){
						exerciseId= rs.getInt(1);
						headline = rs.getString(4);
						question = rs.getString(5);
						rightSelectionPoints = rs.getDouble(6);
						evaluationType = rs.getInt(7);
						maxPointsInExercise = rs.getDouble(8);
						
						exerciseType = 0;
					}else{
						query ="SELECT * from imageMultipleChoiceExercise WHERE examId="+examId+" AND exerciseNumber="+i;
						System.out.println(query);
						rs = st.executeQuery(query);
						if(rs.next()){
							exerciseId= rs.getInt(1);

							headline = rs.getString(4);
							question = rs.getString(5);
							rightSelectionPoints = rs.getDouble(6);
							evaluationType = rs.getInt(7);
							maxPointsInExercise = rs.getDouble(8);
							
							exerciseType = 1;
						}else{
							query ="SELECT * from freeTextExercise WHERE examId="+examId+" AND exerciseNumber="+i;
							System.out.println(query);
							rs = st.executeQuery(query);
							if(rs.next()){
								exerciseId= rs.getInt(1);

								headline = rs.getString(4);
								question = rs.getString(5);
								maxPointsInExercise = rs.getDouble(6);
								
								exerciseType = 2;
							}
						}
					}
					
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				if(exerciseId == -1){
					break;
				}
				
				
				try{
					
					switch (exerciseType) {
						case Variables.MPCHOICE_EXERCISE_TYPE:
							
							query = "SELECT * FROM multipleChoiceQuestion WHERE exerciseId="+exerciseId+" ORDER by id ASC";
							
							Statement st = ds.getStatement();	
							ResultSet rs = st.executeQuery(query);
							
							List<MultipleChoiceQuestion> mpQuestions = new ArrayList<MultipleChoiceQuestion>();
							
							while(rs.next()){
								int mp_id = rs.getInt(1);
								String mp_question = rs.getString(3);
								
								List<String> mp_answers = new ArrayList<String>();
								
								Set<Integer> mp_correct_answers = new HashSet<Integer>();
								
								query = "SELECT * FROM multipleChoiceQuestionAnswerPossibilities WHERE multipleChoiceQuestionId="+mp_id+" ORDER BY answerIndex ASC";
								
								Statement mp_answer_st = ds.getStatement();	
								ResultSet mp_answer_rs = mp_answer_st.executeQuery(query);
								
								while(mp_answer_rs.next()){
									mp_answers.add(mp_answer_rs.getString(2));
								}
								
								query = "SELECT * FROM multipleChoiceQuestionSolutions WHERE multipleChoiceQuestionId="+mp_id;
								
								Statement mp_correct_answer_st = ds.getStatement();	
								ResultSet mp_correct_answer_rs = mp_correct_answer_st.executeQuery(query);
								
								while(mp_correct_answer_rs.next()){
									mp_correct_answers.add(mp_correct_answer_rs.getInt(2));
								}
															
								MultipleChoiceQuestion mpQuestion = new MultipleChoiceQuestion(mp_question, mp_answers, mp_correct_answers);
								
								mpQuestions.add(mpQuestion);							
								
							}
							curExercise = new MultipleChoiceExercise(exerciseId, question, maxPointsInExercise, headline, mpQuestions, rightSelectionPoints, evaluationType);

							exerciseList.push(curExercise);
							break;
	
						case Variables.IMAGE_MPCHOICE_EXERCISE_TYPE:
							
							query = "SELECT * FROM imageMultipleChoiceQuestion WHERE exerciseId="+exerciseId+" ORDER by id ASC";
							
							st = ds.getStatement();	
							rs = st.executeQuery(query);
							
							List<ImageMultipleChoiceQuestion> imageMPQuestions = new ArrayList<ImageMultipleChoiceQuestion>();
							
							while(rs.next()){
								int mp_id = rs.getInt(1);
								String mp_question = rs.getString(3);
								
								List<String> mp_answers = new ArrayList<String>();
								
								Set<Integer> mp_correct_answers = new HashSet<Integer>();
								
								query = "SELECT * FROM imageMultipleChoiceQuestionAnswerPossibilities WHERE imageMultipleChoiceQuestionId="+mp_id+" ORDER BY answerIndex ASC";
								
								Statement mp_answer_st = ds.getStatement();	
								ResultSet mp_answer_rs = mp_answer_st.executeQuery(query);
								
								while(mp_answer_rs.next()){
									mp_answers.add(mp_answer_rs.getString(2));
								}
								
								query = "SELECT * FROM imageMultipleChoiceQuestionSolutions WHERE imageMultipleChoiceQuestionId="+mp_id;
								
								Statement mp_correct_answer_st = ds.getStatement();	
								ResultSet mp_correct_answer_rs = mp_correct_answer_st.executeQuery(query);
								
								while(mp_correct_answer_rs.next()){
									mp_correct_answers.add(mp_correct_answer_rs.getInt(2));
								}
															
								ImageMultipleChoiceQuestion mpQuestion = new ImageMultipleChoiceQuestion(mp_question, mp_correct_answers);
								mpQuestion.setAnswerPossibilitiesLocation(mp_answers);
								
								imageMPQuestions.add(mpQuestion);							
								
							}
							curExercise = new ImageMultipleChoiceExercise(exerciseId, question, maxPointsInExercise, headline, imageMPQuestions, rightSelectionPoints, evaluationType);

							exerciseList.push(curExercise); 
							break;
	
						case Variables.FREE_TEXT_EXERCISE_TYPE: 

							query = "SELECT * FROM freeTextQuestion WHERE exerciseId="+exerciseId+" ORDER by id ASC";
							
							st = ds.getStatement();	
							rs = st.executeQuery(query);
							
							List<FreeTextQuestion> freeTextQuestions = new ArrayList<FreeTextQuestion>();
							
							while(rs.next()){
								
								FreeTextQuestion curFreeTextQuestion = new FreeTextQuestion(rs.getInt(1), rs.getString(3), rs.getString(4), rs.getDouble(5), rs.getDouble(6));
								freeTextQuestions.add(curFreeTextQuestion);											
								
							}
							curExercise = new FreeTextExercise(exerciseId, question, maxPointsInExercise, headline, freeTextQuestions);

							exerciseList.push(curExercise); 
							break;
		
						default:
							break;
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}					
					
					
				
			}
			
			return exerciseList;
		}
		
		
		public void changeGradingKey(int examId, GradingKey gradingKey) {
			String query =  "DELETE FROM exam_grading WHERE examId="+examId;
			
			boolean insertCorrect = execute(query);
			
			if(!insertCorrect)
				return;
			
				
			double[] gradingDouble = 
				{gradingKey.getgrade10(),gradingKey.getgrade13(),gradingKey.getgrade17(),
					gradingKey.getgrade20(),gradingKey.getgrade23(),gradingKey.getgrade27(),
					gradingKey.getgrade30(),gradingKey.getgrade33(),gradingKey.getgrade37(),
					gradingKey.getgrade40(),gradingKey.getgrade43(),gradingKey.getgrade47(),};
			
			String grade="1.0";
	    	for(int i=0; i<gradingDouble.length; i++){
	    		switch (i) {
					case 0:
						grade="1.0";
						break;
					case 1:
						grade="1.3";
						break;
					case 2:
						grade="1.7";
						break;
					case 3:
						grade="2.0";
						break;
					case 4:
						grade="2.3";
						break;
					case 5:
						grade="2.7";
						break;
					case 6:
						grade="3.0";
						break;
					case 7:
						grade="3.3";
						break;
					case 8:
						grade="3.7";
						break;
					case 9:
						grade="4.0";
						break;
					case 10:
						grade="4.3";
						break;
					case 11:
						grade="4.7";
						break;
					default:
						
						break;
						
				}
			    double pointsToArchive = gradingDouble[i];
		    	
	    		insertCorrect = execute("INSERT INTO exam_grading (examId, grade, pointsNeeded) " +
						"VALUES (" + examId+"," +
									"'" + grade  +"'," +
									pointsToArchive
						+")");
	    	}
	    	
	    	WorkedExamDAO workedExamDAO = new WorkedExamDAO();
	    	
	    	workedExamDAO.reloadGradesForWorkedExamOfExamId(examId);
			
			
		}
		

		
		/**
		 * 
		 * @param examId
		 */
		public void removeExam(int examId) {
			ExamDAO examDAO = new ExamDAO();
			examDAO.deleteExamWithId(examId);
		}
		
		/**
		 * This method executes a given sql string!
		 * @param sql
		 * @return
		 */
		private boolean execute(String sql) {

	    	try {
	            Statement stmt = ds.getStatement();
	            stmt.execute(sql);
	            if (stmt != null) {
	                stmt.close();
	            }
	            
	            return true;
	        } catch (SQLException e) {
	        	e.printStackTrace();
	            return false;
	        } finally {
	            ds.close();
	        }
	    }



}
